﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using System.Web.Security;

namespace TeamNitro
{
    public class DBManager_Jowie
    {
        public string Name { set; get; }
        public string AdminNumber { set; get; }
        public string Password { get; set; }
        public string SchoolName { set; get; }
        public string CurrentCourse { set; get; }
        public string CourseCode { set; get; }
        public string CurrentSemester { set; get; }
        public string CurrentYear { set; get; }
        public string HomeAddress { set; get; }
        public string Email { set; get; }
        public int MobileNumber { set; get; }
        public string ElectiveName { set; get; }
        public string ElectiveCode { get; set; }
        public int ElectiveCreditUnit { get; set; }
        public string CDSName { get; set; }
        public string CDSCode { get; set; }
        public string ChangeType { get; set; }
        public int CDSCreditUnit { get; set; }
        public string ChangeCDSCode { get; set; }
        public string ChangeCDSName { get; set; }
        public string ChangeElectiveCode { get; set; }
        public string ChangeElectiveName { get; set; }
        public string Comment { get; set; }

        static string CONNECTIONSTRING = "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";              
        
        public static DBManager_Jowie GetStudentInfo(String StudentID)
        {
            SqlConnection conn = null;
            DBManager_Jowie result = null;
            

            try
            {
                conn=new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();                   

                comm.CommandText = "SELECT * FROM Student WHERE StudentID = @StudentID";
                comm.Parameters.AddWithValue("@StudentID", StudentID);
                    
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    result = new DBManager_Jowie();
                    result.AdminNumber = (String)dr["StudentID"];
                    result.Name = (String)dr["StudentName"];
                    result.Password = (String)dr["Password"];
                    result.MobileNumber = (int)dr["ContactNumber"];
                    result.HomeAddress = (String)dr["Address"];
                    result.Email = (String)dr["Email"];
                    result.CurrentYear = (String)dr["CurrentYear"];
                    result.CurrentSemester = (String)dr["CurrentSem"];
                    result.SchoolName = (String)dr["School"];
                    result.CurrentCourse = (String)dr["CourseName"];
                    result.CourseCode = (String)dr["CourseCode"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            } 
            return result;
        }

        public static void UpdateStudentInfo(DBManager_Jowie updateData) {

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();

                comm.CommandText = "UPDATE Student SET Address=@Address, Email=@Email, ContactNumber=@ContactNumber WHERE StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", updateData.AdminNumber);
                comm.Parameters.AddWithValue("@Address", updateData.HomeAddress);
                comm.Parameters.AddWithValue("@Email", updateData.Email);
                comm.Parameters.AddWithValue("@ContactNumber", updateData.MobileNumber);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally 
            {
                conn.Close();
            }
        }

        public static bool ChangePassword(String id, String oldpassword, String newpassword)
        {
            bool result = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();

                comm.CommandText = "UPDATE Student SET Password = @NewPassword WHERE StudentID = @StudentID And Password=@Password";
                comm.Parameters.AddWithValue("@StudentID", id);
                comm.Parameters.AddWithValue("@NewPassword", FormsAuthentication.HashPasswordForStoringInConfigFile(newpassword, "sha1"));
                comm.Parameters.AddWithValue("@Password", FormsAuthentication.HashPasswordForStoringInConfigFile(oldpassword, "sha1"));
                int rowsAffected = comm.ExecuteNonQuery();
                result = true;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static List<DBManager_Jowie> GetElectiveSubjects (String el) {            
            SqlConnection conn = null;
            List<DBManager_Jowie> electiveList = new List<DBManager_Jowie>();
            
            try 
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT ElectiveCode, ElectiveName FROM ElectiveSubject WHERE CourseCode = @CourseCode";
                comm.Parameters.AddWithValue("@CourseCode", el);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read()) {
                    DBManager_Jowie dbJowie = new DBManager_Jowie();
                    dbJowie.ElectiveCode = (String)dr["ElectiveCode"];
                    dbJowie.ElectiveName = (String)dr["ElectiveName"];
                    electiveList.Add(dbJowie);
                }
                dr.Close();
            } 
            catch (SqlException e)
            {
                throw e;
            } 
            finally {
                conn.Close();
            }
            return electiveList;
        }

        public static bool AddElectiveSubject(DBManager_Jowie selectedElectiveName)
        {
            bool result = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO RequestElectiveSubject (StudentID, ElectiveCode, ElectiveName, Date) VALUES (@StudentID, @ElectiveCode, @ElectiveName, @Date)";
                comm.Parameters.AddWithValue("@StudentID", selectedElectiveName.AdminNumber);
                comm.Parameters.AddWithValue("@ElectiveCode", selectedElectiveName.ElectiveCode);
                comm.Parameters.AddWithValue("@ElectiveName", selectedElectiveName.ElectiveName);
                comm.Parameters.AddWithValue("@Date", DateTime.Now.Date);

                SqlDataReader dr = comm.ExecuteReader();
                result = dr.Read();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static List<DBManager_Jowie> GetCDSSubjects()
        {
            SqlConnection conn = null;
            List<DBManager_Jowie> cdsList = new List<DBManager_Jowie>();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT CDSCode, CDSName FROM CDSSubject";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    DBManager_Jowie dbJowie = new DBManager_Jowie();
                    dbJowie.CDSCode = (String)dr["CDSCode"];
                    dbJowie.CDSName = (String)dr["CDSName"];
                    cdsList.Add(dbJowie);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return cdsList;
        }

        public static bool AddDropCDSSubjects(DBManager_Jowie selectedCDSName)
        {
            bool result = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO RequestCDSSubject (StudentID, CDSCode, CDSName, ChangeType, Date) VALUES (@StudentID, @CDSCode, @CDSName, @ChangeType, @Date)";
                comm.Parameters.AddWithValue("@StudentID", selectedCDSName.AdminNumber);
                comm.Parameters.AddWithValue("@CDSCode", selectedCDSName.CDSCode);
                comm.Parameters.AddWithValue("@CDSName", selectedCDSName.CDSName);
                comm.Parameters.AddWithValue("@ChangeType", selectedCDSName.ChangeType);
                comm.Parameters.AddWithValue("@Date", DateTime.Now.Date);

                SqlDataReader dr = comm.ExecuteReader();
                result = dr.Read();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static List<DBManager_Jowie> CurrentCDSSubjects(String id)
        {
            SqlConnection conn = null;
            List<DBManager_Jowie> cdsList = new List<DBManager_Jowie>();

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT CDSCode,CDSName FROM StudentCDSSubject Where StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID",id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    DBManager_Jowie dbJowie = new DBManager_Jowie();
                    dbJowie.CDSCode = (String)dr["CDSCode"];
                    dbJowie.CDSName = (String)dr["CDSName"];
                    cdsList.Add(dbJowie);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return cdsList;
        }

        public static bool ChangeCDSSubject(DBManager_Jowie changeCDS)
        {
            bool result = false;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO ChangeCDSSubject (StudentID, CurrentCDSCode, CurrentCDSName, ChangeCDSCode, ChangeCDSName, Comment, Date) VALUES (@StudentID, @CurrentCDSCode, @CurrentCDSName, @ChangeCDSCode, @ChangeCDSName, @Comment, @Date)";
                comm.Parameters.AddWithValue("@StudentID", changeCDS.AdminNumber);
                comm.Parameters.AddWithValue("@CurrentCDSCode", changeCDS.CDSCode);
                comm.Parameters.AddWithValue("@CurrentCDSName", changeCDS.CDSName);
                comm.Parameters.AddWithValue("@ChangeCDSCode", changeCDS.ChangeCDSCode);
                comm.Parameters.AddWithValue("@ChangeCDSName", changeCDS.ChangeCDSName);
                comm.Parameters.AddWithValue("@Comment", changeCDS.Comment);
                comm.Parameters.AddWithValue("@Date", DateTime.Now.Date);

                SqlDataReader dr = comm.ExecuteReader();
                result = dr.Read();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static DBManager_Jowie CurrentElectiveSubject(String id)
        {
            SqlConnection conn = null;
            DBManager_Jowie elective = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT ElectiveCode,ElectiveName FROM StudentElectiveSubject Where StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    elective = new DBManager_Jowie();
                    elective.ElectiveCode = (String)dr["ElectiveCode"];
                    elective.ElectiveName = (String)dr["ElectiveName"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return elective;
        }

        public static void ChangeElectiveSubject(DBManager_Jowie changeELE)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO ChangeElectiveSubject (StudentID, CurrentElectiveCode, CurrentElectiveName, ChangeElectiveCode, ChangeElectiveName, Comment, Date) VALUES (@StudentID, @CurrentElectiveCode, @CurrentElectiveName, @ChangeElectiveCode, @ChangeElectiveName, @Comment, @Date)";
                comm.Parameters.AddWithValue("@StudentID", changeELE.AdminNumber);
                comm.Parameters.AddWithValue("@CurrentElectiveCode", changeELE.ElectiveCode);
                comm.Parameters.AddWithValue("@CurrentElectiveName", changeELE.ElectiveName);
                comm.Parameters.AddWithValue("@ChangeElectiveCode", changeELE.ChangeElectiveCode);
                comm.Parameters.AddWithValue("@ChangeElectiveName", changeELE.ChangeElectiveName);
                comm.Parameters.AddWithValue("@Comment", changeELE.Comment);
                comm.Parameters.AddWithValue("@Date", DateTime.Now.Date);

                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable StudentCDS(String ID)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Select CDSName, CDSCode From StudentCDSSubject Where StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", ID);

                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable StudentElective(String ID)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = CONNECTIONSTRING;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Select ElectiveName, ElectiveCode From StudentElectiveSubject Where StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", ID);

                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
    }   
}